﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Net;
using System.Threading;
using System.Web;
using System.Data.OleDb;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable[] dt_rep;
        private delegate void Deleg();

        private struct sp_params
        {
            public string name;
            public object value;
        }

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();

            return true;
        }

        private DataTable[] dt_site = new DataTable[0];

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOlapDW;Integrated Security=True");
                conn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }

            
        }
        private void SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            try
            {
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private DataTable[] SqlProcExecWithQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            DataTable[] dt = new DataTable[0];
            try
            {
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                SqlDataReader dr = com.ExecuteReader();
                 
                while (dr.HasRows)
                {
                    Array.Resize(ref dt, dt.Length + 1);
                    dt[dt.Length - 1] = new DataTable();
                    dt[dt.Length - 1].Load(dr);
                    if (dt[dt.Length - 1].Columns.Count > 2) dt[dt.Length - 1].TableName = dt[dt.Length - 1].Rows[0][1].ToString();
                    if (dr.IsClosed) break;
                }

                dr.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }


        private void StartCreateRep(object sender, EventArgs e)
        {
            //dataGridView1.DataSource = null;
            //dt_rep.Clear();
            //del = new Deleg(CreateRep);
            //this.Text += " Формируется";
            //IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            //panel1.Enabled = false;
            //dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            //del.EndInvoke(aRes);
            //panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            //dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            //this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            //dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            //if (conn.State == ConnectionState.Closed) conn.Open();
            //try
            //{
                
            //    dt_rep.Load(RepProc(conn));

            //    conn.Close();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.ToString());
            //    conn.Close();
            //}

        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "prays_dlya_sayta3";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, false);
                MessageBox.Show("Результат сохранен");
            }
            
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.UTF8);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        if (col.Index < table.ColumnCount - 1) sw.Write(col.HeaderText + ";");
                        else sw.Write(col.HeaderText);
                        
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        if (y < col_n.Count - 1)
                            sw.Write(table[col_n[y], i].Value.ToString() + ";");
                        else sw.Write(table[col_n[y], i].Value.ToString());
                    sw.Write("\r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public static bool SaveDGVToCSVfile(string filename, DataTable table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataColumn col in table.Columns)
                {
                    if (col.Ordinal < table.Columns.Count - 1) sw.Write(col.ColumnName + ";");
                    else sw.Write(col.ColumnName);
                    col_n.Add(col.Ordinal);
                }
                sw.WriteLine();
                int x = table.Rows.Count;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        if (y < col_n.Count - 1)
                            sw.Write(table.Rows[i][col_n[y]] + ";");
                        else sw.Write(table.Rows[i][col_n[y]]);
                    sw.Write("\r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public static bool SaveDGVToCSVfile(string filename, DataTable[] table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);
                foreach (DataTable tbl in table)
                {
                    List<int> col_n = new List<int>();
                    foreach (DataColumn col in tbl.Columns)
                    {
                        if (col.Ordinal < tbl.Columns.Count - 1) sw.Write(col.ColumnName + ";");
                        else sw.Write(col.ColumnName);
                        col_n.Add(col.Ordinal);
                    }
                    sw.WriteLine();
                    int x = tbl.Rows.Count;

                    for (int i = 0; i < x; i++)
                    {
                        for (int y = 0; y < col_n.Count; y++)
                            if(y < col_n.Count - 1)
                                sw.Write(tbl.Rows[i][col_n[y]] + ";");
                            else sw.Write(tbl.Rows[i][col_n[y]]);
                        sw.Write("\r\n");
                    }
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }



        private void dataGridView1_DataSourceChanged(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource == null) toolStripStatusLabel1.Text = "";
            else toolStripStatusLabel1.Text = "Результатов: " + dataGridView1.RowCount.ToString();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            try
            {
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                dt_rep = null;
                sp_params[] par = new sp_params[1];
                //par[0].name = "@drugcode";
                //par[0].value = null; //datatable

                par[0].name = "@druglist";
                par[0].value = null; //datatable

                if (MessageBox.Show("Загрузить товары из прайса поставщика?", "", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    OpenFileDialog opf = new OpenFileDialog();
                    opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                    if (opf.ShowDialog() == DialogResult.OK)
                    {                
                        string filename = opf.FileName;
                        string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                        DataSet ds = new DataSet("EXCEL");
                        OleDbConnection cn = new OleDbConnection(ConStr);

                        if (cn.State == ConnectionState.Closed) cn.Open();
                        DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { }); //{ null, null, null, null }
                        string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                        string select = String.Format("SELECT * FROM [{0}]", sheet1);
                        OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                        ad.Fill(ds);
                        DataTable tb = ds.Tables[0];
                        cn.Close();
                        par[0].value = tb;
                    }
                }

                dt_rep = SqlProcExecWithQuery(conn, "pas_Site", 300, par);
                dataGridView1.DataSource = null;
                dataGridView1.DataSource = dt_rep[0].DefaultView;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        

    }

}
